package studentsystem;

import java.sql.*;

public class Subject {
    private double sum, max, min;
    SQL bd = new SQL();
    Connection conn = bd.getConn();

    Subject() {
        System.out.println();
        System.out.println("\t科目\t\t\t"+"平均成绩\t\t" +  "最高成绩\t\t" + "最低成绩");
        try {
            Statement stmt = conn.createStatement(); //创建Statement对象
            int i = 0;
            String sql = "select math from students order by math desc;";    //要执行的SQL
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                sum = sum + rs.getDouble("math");
                i++;
                if (rs.isFirst())
                    max = rs.getDouble("math");
                if (rs.isLast())
                    min = rs.getDouble("math");
            }
            sum = sum / i;//平均成绩
            System.out.println("应用数学成绩\t\t\t" + String.format("%.2f", sum) + "\t\t" + max + "\t\t" + min);

            i = 0;
            sum = 0.0;
            String sql1 = "select English from students order by English desc;";    //要执行的SQL
            ResultSet rs1 = stmt.executeQuery(sql1);
            while (rs1.next()) {
                sum = sum + rs1.getDouble("English");
                i++;
                if (rs1.isFirst())
                    max = rs1.getDouble("English");
                if (rs1.isLast())
                    min = rs1.getDouble("English");
            }
            sum = sum / i; //平均成绩
            System.out.println("大学英语成绩\t\t\t" + String.format("%.2f", sum) + "\t\t" + max + "\t\t" + min);

            i = 0;
            sum = 0.0;
            //数据库
            String sql2 = "select Java from students order by Java desc;";    //要执行的SQL
            ResultSet rs2 = stmt.executeQuery(sql2);
            while (rs2.next()) {
                sum = sum + rs2.getDouble("Java");
                i++;
                if (rs2.isFirst())
                    max = rs2.getDouble("Java");
                if (rs2.isLast())
                    min = rs2.getDouble("Java");
            }
            sum = sum / i;//平均成绩
            System.out.println("Java程序设计成绩\t\t" + String.format("%.2f", sum) + "\t\t" + max + "\t\t" + min);

            i = 0;
            sum = 0.0;
            String sql3 = "select computer from students order by computer desc;";    //要执行的SQL
            ResultSet rs3 = stmt.executeQuery(sql3);
            while (rs3.next()) {
                sum = sum + rs3.getDouble("computer");
                i++;
                if (rs3.isFirst())
                    max = rs3.getDouble("computer");
                if (rs3.isLast())
                    min = rs3.getDouble("computer");
            }
            sum = sum / i;                //平均成绩
            System.out.println("计算机应用基础成绩\t" + String.format("%.2f", sum) + "\t\t" + max + "\t\t" + min);
            System.out.println();

            rs.close();
            rs1.close();
            rs2.close();
            rs3.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        new Subject();
    }
}